<?php

namespace app\admin\model;

use think\Model;
use think\Db;
use traits\model\SoftDelete;

class Customer extends Model
{
    use SoftDelete;
    protected $deleteTime = 'delete_time';

    /**
     * 返回所有数据
     * @return string
     */
    public function listAll(){
        return json_encode(Customer::field('id,name,tel,address,brand,step,cid,date,seller')->select(), JSON_UNESCAPED_UNICODE);
    }

    /**
     * @param $data
     * @return array|string
     * 保存数据
     */
    public function saveDate($data)
    {
        $result = $this->validate(true)->allowField(true)->save($data);
        if(false === $result){
            $error = $this->getError();
            return array('code'=>0,'msg'=>$error);
        }else{
            return 'success';
        }
    }

    /**
     * @param $id
     * @return array
     * 根据ID删除
     */
    public function deleteById($id)
    {
        $result = Customer::destroy($id);
        if ($result > 0) {
            return array('code'=>1,'msg'=>'删除成功');
        }
    }

    /**
     * @param array $data
     * @return array|string
     * 编辑数据
     */
    public function edit(array $data = [])
    {
        $result = $this->validate(true)->allowField(true)->save($data,['id'=>$data['id']]);
        if(false === $result){
            $error = $this->getError();
            return array('code'=>0,'msg'=>$error);
        }else{
            return 'success';
        }
    }

    /**
     * @return mixed
     * 曲线图中的售卡部分
     */
    public function chartLine(){
        $count = $this->field('date,count(id) AS value')
            ->where('date','between','2016-11-04,2016-11-22')
            ->group('date')
            ->select()
            ->toArray();
        $data1 = array_column($count, 'date');
        $data2 = array_column($count, 'value');

        foreach ($data1 as $key => $val) {
            $c_data[$key]['kind'] = '售卡';
            $c_data[$key]['date'] = $data1[$key];
            $c_data[$key]['value'] = $data2[$key];
        }
        return $c_data;
    }

    /**
     * @return mixed
     * 曲线图中的带单部分
     */
    public function chartLineD(){
        $count = $this
            ->join('tp_order','tp_customer.cid = tp_order.cid')
            ->field('tp_order.date,count(tp_order.id) AS value')
            ->where('tp_customer.brand','EXP','<> tp_order.brand')
            ->where('tp_order.date','between','2016-11-04,2016-11-22')
            ->group('tp_order.date')
            ->select()
            ->toArray();

        $data1 = array_column($count, 'date');
        $data2 = array_column($count, 'value');

        foreach ($data1 as $key => $val) {
            $d_data[$key]['kind'] = '带单';
            $d_data[$key]['date'] = $data1[$key];
            $d_data[$key]['value'] = $data2[$key];
        }
        return $d_data;
    }

    /**
     * @return array
     * 统计数据中的售卡部分
     */
    public function countId(){
        $count = $this->field('tp_brand.brand,count(c.id) AS num')
            ->alias('c')
            ->join('tp_brand','tp_brand.brand = c.brand','RIGHT')
            ->group('tp_brand.brand')
            ->order('convert(tp_brand.brand using gbk) asc')
            ->select()
            ->toArray();
        return $count;
    }

    /**
     * @return mixed
     * 统计数据中的带单部分
     */
    public function countD(){
        $query = $this
            ->join('tp_order','tp_order.cid = tp_customer.cid')
            ->field('tp_customer.brand as b,tp_order.date as d,tp_order.id as i')
            ->where('tp_customer.brand','EXP','<> tp_order.brand')
            ->buildSql();

        $count = db('brand')
            ->alias('a')
            ->join([$query=> 'w'], 'a.brand = w.b','LEFT')
            ->field("a.brand,count(w.i) AS 'num'")
            ->group('a.brand')
            ->order('convert(a.brand using gbk) asc')
            ->select();
        return $count;
    }

    /**
     * @param $date
     * @return mixed
     * 根据日期统计中的售卡部分
     */
    public function dateCount($date){
        $count = Db::query("SELECT
tp_brand.brand,
Count(tp_customer.id) AS num
FROM
tp_brand
LEFT JOIN tp_customer ON tp_brand.brand = tp_customer.brand AND tp_customer.date = '$date'
WHERE tp_customer.delete_time IS NULL
GROUP BY
tp_brand.brand
ORDER BY
CONVERT (tp_brand.brand USING gbk) ASC");
        return $count;
    }

    /**
     * @param $date
     * @return mixed
     * 根据日期统计中的带单部分
     */
    public function dateCountD($date){
        $count = Db::query("SELECT
	a.brand,
	count(w.i) AS 'num'
FROM
    tp_brand a
LEFT JOIN (
	SELECT
		tp_customer.brand AS b,
		tp_order.date AS d,
		tp_order.id AS i
	FROM
		tp_customer
	INNER JOIN tp_order ON tp_order.cid = tp_customer.cid AND tp_order.date = '$date'
	WHERE tp_customer.brand <> tp_order.brand
	AND `tp_customer`.`delete_time` IS NULL
) w ON a.brand = w.b
GROUP BY
	a.brand
ORDER BY
	CONVERT (a.brand USING gbk) ASC");
        return $count;
    }
}
